package importExcel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.tern.data.Entity;
import com.tern.data.FormatException;
import com.tern.data.Record;
import com.tern.db.DBException;

import java.io.*;

public class ReadExcel {
	@SuppressWarnings({ "deprecation", "unused" })
	public static void readExcelToDatabase(String excelpath) {
		Entity entity = Entity.createFromSchema("student");
		Record srow;
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		try {
			fs = new POIFSFileSystem(new FileInputStream(excelpath));
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
		}
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = null;
		HSSFCell cell = null;

		

		int rowNum, cellNum;
		rowNum = sheet.getLastRowNum();
		for (int i = 1; i < rowNum+1; i++) {
			int stuNum = 0;
			String stuName = "";
			String stuPSW = "123";
			int grade = 0;
			String sex = "";
			String major = "";
			String college = "";
			String stuPhone = "";
			String stuMail = "";
			String remarks = "";
			
			row = sheet.getRow(i);
			cell = row.getCell((short) 0);
			stuNum = (int) cell.getNumericCellValue();
			System.out.println(stuNum);
			cell = row.getCell((short) 1);
			stuName = cell.getStringCellValue();
			System.out.println(stuName);
			cell = row.getCell((short) 2);
			grade = (int) cell.getNumericCellValue();
			System.out.println(grade);
			cell = row.getCell((short) 3);
			sex = cell.getStringCellValue();
			System.out.println(sex);
			cell = row.getCell((short) 4);
			major = cell.getStringCellValue();
			System.out.println(major);
			cell = row.getCell((short) 5);
			college = cell.getStringCellValue();
			System.out.println(college);
			cell = row.getCell((short) 6);
			remarks = cell.getStringCellValue();
			System.out.println(remarks);

			srow = entity.createRow();
			try {
				srow.set("stuNum", stuNum);
				srow.set("stuName", stuName);
				srow.set("stuPSW", stuPSW);
				srow.set("grade", grade);
				srow.set("sex", sex);
				srow.set("major", major);
				srow.set("college", college);
				srow.set("stuPhone", stuPhone);
				srow.set("stuMail", stuMail);
				srow.set("remarks", remarks);
				entity.addRow(srow);			
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		try {
			entity.updateToDB();
		} catch (DBException e) {
			e.printStackTrace();
		}
	}
}
